package intranet.models;
import java.sql.*;
public class VideoModel {
	Connection DBConnection=null;
	public VideoModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	public ResultSet SelectVideoById(int file_id){
		ResultSet result=null;
		String Conditional_String=(file_id!=0)?" WHERE F.FILE_ID="+file_id:"";
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
			Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
			Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM VIDEO_FILES F INNER JOIN ";
			Sql+="FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID "+Conditional_String+" ORDER BY F.DATE_UPLOAD DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet SelectTopVideo(){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
			Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
			Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID ORDER BY F.VIEW_NUMBER DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public int RowCount(ResultSet rs) throws SQLException{
		int result=0;
		while(rs.next()){
			result++;
		}
		return result;
	}
	
	public ResultSet SelectOtherVideo() throws SQLException{
		ResultSet result=null;
		int i=0;
		int RowCount=this.RowCount(this.SelectTopVideo());
		int EndIndex=0;
		String Condition=(RowCount>0)?" WHERE ":"";
		/* Generate CONDITIONAL STRING TO AVOID SELECTING THE SAME COURSE AS TOP COURSE */
		EndIndex=(RowCount>=8)?7:RowCount-1;
		ResultSet TopVideo=this.SelectTopVideo();
		while(TopVideo.next()){
			if(i<EndIndex){
				Condition+="F.FILE_ID!="+TopVideo.getInt("FILE_ID")+" AND ";
			}
			if(i==EndIndex){
				Condition+="F.FILE_ID!="+TopVideo.getInt("FILE_ID");
			}
			i++;
		}

		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
			Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
			Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID "+Condition+" ORDER BY F.VIEW_NUMBER DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/*public ResultSet SearchVideo(int cat_id,String keywords){
		ResultSet result=null;
		String Sql="";
		if(cat_id==0){			
			if(keywords!=""){
				Sql="SELECT DISTINCT * FROM (SELECT F.FILE_ID,F.ITEM_ID,I.ITEM_NAME,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM ((LEARNING_FILES F INNER JOIN LEARNING_ITEMS I ";
				Sql+="ON F.ITEM_ID=I.ITEM_ID) INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) INNER JOIN LEARNING_FILE_KEYWORDS K ON F.FILE_ID=K.FILE_ID ";
				Sql+="WHERE lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(F.FILE_TITLE) LIKE'%"+keywords.toLowerCase()+"%' ORDER BY F.VIEW_NUMBER DESC)";
			}else{
				Sql="SELECT DISTINCT * FROM (SELECT F.FILE_ID,F.ITEM_ID,I.ITEM_NAME,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM ((LEARNING_FILES F INNER JOIN LEARNING_ITEMS I ";
				Sql+="ON F.ITEM_ID=I.ITEM_ID) INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+=" ORDER BY F.VIEW_NUMBER DESC)";
			}	
			
		}else{
			if(keywords!=""){
				Sql="SELECT DISTINCT * FROM (SELECT F.FILE_ID,F.ITEM_ID,I.ITEM_NAME,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM ((LEARNING_FILES F INNER JOIN LEARNING_ITEMS I ";
				Sql+="ON F.ITEM_ID=I.ITEM_ID) INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) INNER JOIN LEARNING_FILE_KEYWORDS K ON F.FILE_ID=K.FILE_ID ";
				Sql+="WHERE (lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(F.FILE_TITLE) LIKE'%"+keywords.toLowerCase()+"%') AND F.CATEGORY_ID="+cat_id+" ORDER BY F.VIEW_NUMBER DESC)";
			}else{
				Sql="SELECT DISTINCT * FROM (SELECT F.FILE_ID,F.ITEM_ID,I.ITEM_NAME,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM ((LEARNING_FILES F INNER JOIN LEARNING_ITEMS I ";
				Sql+="ON F.ITEM_ID=I.ITEM_ID) INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+="WHERE F.CATEGORY_ID="+cat_id+" ORDER BY F.VIEW_NUMBER DESC)";
			}	
		}
		try{
			Statement ConStatement=DBConnection.createStatement();
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	*/
	
	public ResultSet SearchVideo(int cat_id,String keywords){
		ResultSet result=null;
		String Sql="";
		if(cat_id==0){			
			if(keywords!=""){
				Sql+="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM (VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+="LEFT JOIN VIDEO_KEYWORDS K ON F.FILE_ID=K.FILE_ID WHERE lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(F.FILE_TITLE) LIKE'%"+keywords.toLowerCase()+"%' ORDER BY F.VIEW_NUMBER DESC";
			}else{
				Sql+="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM (VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+="LEFT JOIN VIDEO_KEYWORDS K ON F.FILE_ID=K.FILE_ID ORDER BY F.VIEW_NUMBER DESC";
			}	
			
		}else{
			if(keywords!=""){	
				Sql+="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM (VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+="LEFT JOIN VIDEO_KEYWORDS K ON F.FILE_ID=K.FILE_ID WHERE (lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(F.FILE_TITLE) LIKE'%"+keywords.toLowerCase()+"%') AND F.CATEGORY_ID="+cat_id+" ORDER BY F.VIEW_NUMBER DESC";
			}else{
				Sql+="SELECT F.FILE_ID,F.CATEGORY_ID,C.CATEGORY_NAME,F.FILE_TITLE,";
				Sql+="F.FILE_DESC,F.FILE_NAME,F.FILE_PATH,F.FILE_SIZE,F.IMAGE_NAME,F.IMAGE_PATH,F.VIEW_NUMBER,";
				Sql+="F.PREPARED_BY,F.DATE_UPLOAD,F.USER_UPLOAD FROM (VIDEO_FILES F INNER JOIN FILE_CATEGORIES C ON F.CATEGORY_ID=C.CATEGORY_ID) ";
				Sql+="LEFT JOIN VIDEO_KEYWORDS K ON F.FILE_ID=K.FILE_ID WHERE F.CATEGORY_ID="+cat_id+" ORDER BY F.VIEW_NUMBER DESC";
			}	
		}
		try{
			Statement ConStatement=DBConnection.createStatement();
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
}
